USE pubs

-- system views show no existing keys
SELECT * FROM sys.certificates
SELECT * FROM sys.key_encryptions
SELECT * FROM sys.symmetric_keys

-- create the master key for the database, this will be used to encrypt every other key in the DB and is in turn encrypted by the service master key
CREATE MASTER KEY ENCRYPTION BY password = 'MasterKey1$'
--ALTER MASTER KEY ENCRYPTION password = 'foo!0Test'

-- create certificates for each user to be used to protect their own symmetric keys
-- symmetric keys are created to encrypt data because they are faster and do not have inherient data size limitations based on key size
CREATE CERTIFICATE acct_cert AUTHORIZATION acct WITH SUBJECT = 'Accounting Cert'

CREATE CERTIFICATE sales_cert AUTHORIZATION sales WITH SUBJECT = 'Sales Certificate'

SELECT * FROM sys.certificates

-- create symmetric keys for each user to protect thier data
-- not if running SQL Server on XP the AES algorithms are not available, use must use 3DES
CREATE SYMMETRIC KEY acct_key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE acct_cert

CREATE SYMMETRIC KEY sales_key WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE sales_cert

-- grant permissions to the symmetric keys so that only the correct user can access them
GRANT VIEW DEFINITION ON SYMMETRIC KEY::acct_key TO acct
GRANT VIEW DEFINITION ON SYMMETRIC KEY::sales_key TO sales

SELECT * FROM sys.symmetric_keys

-- now add the columns to contain encrypted data
ALTER TABLE dbo.authors ADD enc_ssn varbinary(max)
ALTER TABLE dbo.authors ADD enc_bat_phone varbinary(max)
ALTER TABLE dbo.authors ADD enc_commission varbinary(max)
ALTER TABLE dbo.authors ADD password_salt uniqueidentifier DEFAULT NEWID()
ALTER TABLE dbo.authors ADD password_hash varbinary(max)

drop table dbo.author_draft 
-- add a table for storing encrypted rough drafts by author
CREATE TABLE dbo.author_draft (
	draft_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	au_id varchar(11) NOT NULL REFERENCES dbo.authors(au_id), 
	title varchar(100) NOT NULL,
	data varbinary(max)	NULL
)
GO

CREATE PROCEDURE dbo.ListDrafts
	@au_id varchar(11)
AS
BEGIN
	SELECT draft_id, title from dbo.author_draft WHERE au_id = @au_id
END
GO

GRANT EXECUTE ON dbo.ListDrafts TO web
GO

